﻿'Microsoft SQL Server Compact 3.5 Books Online and Samples
'http://www.microsoft.com/downloads/details.aspx?FamilyID=1ff0529a-eb1f-4044-b4b7-40b00710f7b7&displaylang=en
'Learn from The Master, Steve Lasker
'http://blogs.msdn.com/stevelasker/archive/2008/11/25/demos-presentations-links-screencasts-and-videos-for-sql-server-compact.aspx
Imports System.Data
Imports System.Collections.ObjectModel
Imports BusinessEntityObjects
Imports System.Data.SqlServerCe
Imports Ocean.OceanFramework.SQLCompactDataAccess
Imports Ocean.OceanFramework.SQLCompactDataAccess.DataAccessHelper  'C# can't do this.  I'm importing a Class, so I can access it's shared methods directly.

Public Class ContactDAL
    Public Const CONNECTION_STRING As String = "Data Source = WPFDisciples.sdf"

#Region " Declarations "

    Private _objEntityLoader As New EntityLoader(Of Contact)(CONNECTION_STRING)

#End Region

#Region " Constructors "

    Public Sub New()
    End Sub

#End Region

#Region " Methods "

    Public Function Count() As Integer
        Dim da As New DataAccess(CONNECTION_STRING)
        Return CType(da.ExecuteScaler(My.Resources.StoredProcedures.ContactCount), Integer)
    End Function

    Public Shared Function CreateInstance() As ContactDAL
        Return New ContactDAL
    End Function

    Public Function Delete(ByVal obj As Contact) As Integer

        If Not obj.HasBeenValidated Then
            Throw New InvalidOperationException("Contact has not yet been validated for delete.")
        End If

        Dim da As New DataAccess(CONNECTION_STRING)
        Return da.ExecuteNonQuery(My.Resources.StoredProcedures.ContactDelete, BuildSQLParameter("@ID", SqlDbType.Int, obj.Id))
    End Function

    Public Function FillDropDown() As List(Of ContactSearchResult)

        Dim objFillDropDownEntityLoader As New EntityLoader(Of ContactSearchResult)(CONNECTION_STRING)
        Return objFillDropDownEntityLoader.LoadList(My.Resources.StoredProcedures.ContactFillDropDown, CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    End Function

    Public Function GetFeeds() As List(Of ContactFeed)

        Dim objGetFeedsEntityLoader As New EntityLoader(Of ContactFeed)(CONNECTION_STRING)
        Return objGetFeedsEntityLoader.LoadList(My.Resources.StoredProcedures.ContactGetAllFeeds, CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    End Function

    Public Function Insert(ByVal obj As Contact, ByRef intNewId As Integer) As Integer

        If Not obj.HasBeenValidated Then
            Throw New InvalidOperationException("Contact has not yet been validated for insert.")
        End If

        Using cn As New SqlCeConnection(CONNECTION_STRING)
            cn.Open()

            Dim da As New DataAccess(cn)
            Dim intAffectedRows As Integer = da.ExecuteNonQuery(My.Resources.StoredProcedures.ContactInsert, BuildSQLParameter("@FIRSTNAME", SqlDbType.NVarChar, 30, obj.FirstName), BuildSQLParameter("@LASTNAME", SqlDbType.NVarChar, 30, obj.LastName), BuildSQLParameter("@BIRTHDAY", SqlDbType.DateTime, True, IIf(obj.Birthday.HasValue, obj.Birthday, DBNull.Value)), BuildSQLParameter("@NUMBEROFCOMPUTERS", SqlDbType.Int, obj.NumberOfComputers), BuildSQLParameter("@BLOGURL", SqlDbType.NVarChar, 100, obj.BlogURL), BuildSQLParameter("@FEEDURL", SqlDbType.NVarChar, 100, obj.FeedURL), BuildSQLParameter("@COMMENT", SqlDbType.NVarChar, 50, obj.Comment))

            If intAffectedRows = 1 Then
                intNewId = CType(da.ExecuteScaler(My.Resources.StoredProcedures.SelectLastIdentity), Integer)

                If intNewId <> 0 Then
                    Return 1

                Else
                    Return 0
                End If

            End If

        End Using
        Return 0
    End Function

    Public Function SearchByLastName(ByVal strSearchString As String) As List(Of ContactSearchResult)

        Dim objSearchResultsEntityLoader As New EntityLoader(Of ContactSearchResult)(CONNECTION_STRING)
        Return objSearchResultsEntityLoader.LoadList(My.Resources.StoredProcedures.ContactSearchByLastName, CommandBehavior.CloseConnection Or CommandBehavior.SingleResult, BuildSQLParameter("@SEARCHSTRING", SqlDbType.NVarChar, 30, strSearchString))
    End Function

    Public Function [Select](ByVal intId As Integer) As Contact
        Return _objEntityLoader.LoadOne(My.Resources.StoredProcedures.ContactSelectById, CommandBehavior.CloseConnection Or CommandBehavior.SingleResult Or CommandBehavior.SingleRow, BuildSQLParameter("@ID", SqlDbType.Int, intId))
    End Function

    Public Function SelectAll() As ObservableCollection(Of Contact)
        Return _objEntityLoader.LoadObservableColleciton(My.Resources.StoredProcedures.ContactSelectAll, CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    End Function

    Public Function Update(ByVal obj As Contact) As Integer

        If Not obj.HasBeenValidated Then
            Throw New InvalidOperationException("Contact has not yet been validated for update.")
        End If

        Dim da As New DataAccess(CONNECTION_STRING)
        Return da.ExecuteNonQuery(My.Resources.StoredProcedures.ContactUpdate, BuildSQLParameter("@ID", SqlDbType.Int, obj.Id), BuildSQLParameter("@FIRSTNAME", SqlDbType.NVarChar, 30, obj.FirstName), BuildSQLParameter("@LASTNAME", SqlDbType.NVarChar, 30, obj.LastName), BuildSQLParameter("@BIRTHDAY", SqlDbType.DateTime, True, IIf(obj.Birthday.HasValue, obj.Birthday, DBNull.Value)), BuildSQLParameter("@NUMBEROFCOMPUTERS", SqlDbType.Int, obj.NumberOfComputers), BuildSQLParameter("@BLOGURL", SqlDbType.NVarChar, 100, obj.BlogURL), BuildSQLParameter("@FEEDURL", SqlDbType.NVarChar, 100, obj.FeedURL), BuildSQLParameter("@COMMENT", SqlDbType.NVarChar, 50, obj.Comment))
    End Function

#End Region

#Region " Create Database Method "

    'TODO - developers uncomment to recreate database
    'Public Sub CreateDataBase()
    '    Dim intX As Integer
    '    Dim obj As Contact
    '    Dim da As New DataAccess(CONNECTION_STRING)
    '    da.ExecuteNonQuery("DELETE FROM Contact")
    '    da = Nothing
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.vertigo.com/personal/AlanL/Blog/default.aspx", .FeedURL = "http://blogs.vertigo.com/personal/alanl/Blog/rss.xml", .FirstName = "Alan", .LastName = "Le", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://agsmith.wordpress.com/", .FeedURL = "http://agsmith.wordpress.com/feed/", .FirstName = "Andrew", .LastName = "Smith", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://bea.stollnitz.com/blog/", .FeedURL = "http://bea.stollnitz.com/blog/?feed=rss2", .FirstName = "Beatriz ", .LastName = "Stollnitz", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://wekempf.spaces.live.com/", .FeedURL = "http://wekempf.spaces.live.com/feed.rss", .FirstName = "Bill ", .LastName = "Kempf ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://x-coders.com/blogs/sneaky/default.aspx", .FeedURL = "http://x-coders.com/blogs/sneaky/rss.aspx", .FirstName = "Brennon ", .LastName = "Williams ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://www.scottlogic.co.uk/blog/wpf/", .FeedURL = "http://www.scottlogic.co.uk/blog/wpf/feed/", .FirstName = "Colin ", .LastName = "Eberhardt ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.ugidotnet.org/ccavalli/Default.aspx", .FeedURL = "http://blogs.ugidotnet.org/ccavalli/Rss.aspx", .FirstName = "Corrado ", .LastName = "Cavalli ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://www.drwpf.com/blog/Home/tabid/36/BlogId/1/Default.aspx", .FeedURL = "http://www.drwpf.com/blog/Home/tabid/36/rssid/1/Default.aspx", .FirstName = "Dr.", .LastName = "WPF", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://eric.burke.name/dotnetmania/", .FeedURL = "http://feeds.feedburner.com/dotnetmania", .FirstName = "Eric ", .LastName = "Burke ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://www.interact-sw.co.uk/iangblog/", .FeedURL = "http://www.interact-sw.co.uk/iangblog/rss2.0", .FirstName = "Ian ", .LastName = "Griffiths ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.msdn.com/jaimer/default.aspx", .FeedURL = "http://blogs.msdn.com/jaimer/rss.xml", .FirstName = "Jaime ", .LastName = "Rodriguez ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://jmorrill.hjtcentral.com/Home/tabid/428/BlogId/14/Default.aspx", .FeedURL = "http://jmorrill.hjtcentral.com/Home/tabid/428/rssid/14/Default.aspx", .FirstName = "Jeremiah ", .LastName = "Morrill ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.msdn.com/johngossman/default.aspx", .FeedURL = "http://blogs.msdn.com/johngossman/rss.xml", .FirstName = "John ", .LastName = "Gossman ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://joshsmithonwpf.wordpress.com/", .FeedURL = "http://joshsmithonwpf.wordpress.com/feed/", .FirstName = "Josh", .LastName = "Smith ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://karlshifflett.wordpress.com/", .FeedURL = "http://blog.identitymine.com/blogs/joshwagoner/rss.aspx", .FirstName = "Josh ", .LastName = "Wagoner ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://kentb.blogspot.com/", .FeedURL = "http://kentb.blogspot.com/feeds/posts/default", .FirstName = "Kent ", .LastName = "Boogaart ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blog.galasoft.ch/Default.aspx", .FeedURL = "http://feeds.feedburner.com/galasoft?format=xml", .FirstName = "Laurent ", .LastName = "Bugnion ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.msdn.com/llobo/default.aspx", .FeedURL = "http://blogs.msdn.com/llobo/rss.xml", .FirstName = "Lester", .LastName = "Lebo ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://www.julmar.com/blog/mark/", .FeedURL = "http://www.julmar.com/blog/mark/SyndicationService.asmx/GetRss", .FirstName = "Mark ", .LastName = "Smith ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://marlongrech.wordpress.com/", .FeedURL = "http://feeds.feedburner.com/WPFDisciples?format=xml", .FirstName = "Marlon ", .LastName = "Grech ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.msdn.com/mwinkle/default.aspx", .FeedURL = "http://feeds.feedburner.com/wfguy", .FirstName = "Matt ", .LastName = "Winkle ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://mbrownchicago.spaces.live.com/", .FeedURL = "http://mbrownchicago.spaces.live.com/feed.rss", .FirstName = "Mike ", .LastName = "Brown ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://designerslove.net/", .FeedURL = "http://designerslove.net/?feed=rss", .FirstName = "Nate ", .LastName = "Dunlap ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blog.pixelingene.com/", .FeedURL = "http://feeds.feedburner.com/Pixel-In-Gene", .FirstName = "Pavan ", .LastName = "Podila ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blois.us/blog/", .FeedURL = "http://blois.us/blog/atom.xml", .FirstName = "Pete ", .LastName = "Blois ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://peteohanlon.wordpress.com/", .FeedURL = "http://peteohanlon.wordpress.com/feed/", .FirstName = "Pete ", .LastName = "O'Hanlon ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://www.robzelt.com/blog/", .FeedURL = "http://www.robzelt.com/blog/SyndicationService.asmx/GetRss", .FirstName = "Rob ", .LastName = "Zelt ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blog.nerdplusart.com/", .FeedURL = "http://feeds.feedburner.com/nerdplusart?format=xml", .FirstName = "Robby ", .LastName = "Ingebretsen ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://dotnet.org.za/rudi/default.aspx", .FeedURL = "http://dotnet.org.za/rudi/rss.aspx", .FirstName = "Rudi ", .LastName = "Grobler ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://sachabarber.net/", .FeedURL = "http://sachabarber.net/?feed=rss2", .FirstName = "Sacha ", .LastName = "Barber ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://wildermuth.com/", .FeedURL = "http://feeds.feedburner.com/ShawnWildermuth", .FirstName = "Shawn ", .LastName = "Wildermuth ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://khason.net/", .FeedURL = "http://rss.khason.net/microsft", .FirstName = "Tamir ", .LastName = "Khason ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://blogs.msdn.com/vinsibal/default.aspx", .FeedURL = "http://blogs.msdn.com/vinsibal/rss.xml", .FirstName = "Vincent ", .LastName = "Sibal ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://wpfwonderland.wordpress.com/", .FeedURL = "http://wpfwonderland.wordpress.com/feed/", .FirstName = "Walt ", .LastName = "Ritscher ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    '    obj = New Contact With {.ActiveRuleSet = "Insert", .BlogURL = "http://shevaspace.blogspot.com/", .FeedURL = "http://shevaspace.blogspot.com/feeds/posts/default", .FirstName = "Zhou ", .LastName = "Yong ", .NumberOfComputers = 1}
    '    If obj.IsValid Then
    '        Insert(obj, intX)
    '    Else
    '        Throw New InvalidOperationException("Invalid Contact")
    '    End If
    'End Sub
#End Region

End Class
